/*******************************************************************************

This code file produces Table 1, "How Do 421-a Onsite Inclusionary Units Compare to Other Social Housing?"

*******************************************************************************/

*** Manage settings

	run "$dir/code/modules/settings.do"
	
* Load Stata-TeX

	do "$code/modules/stata-tex.do"
	cd "$tables/estimates"
	
********************************************************************************
* 421-a
********************************************************************************

*** Load data

	use "$data/clean/cleaned_data.dta", clear

	* Expand to match characteristics of 421-a inclusionary units
	keep if inclusionary_onsite == 1
	expand unitsres
	
*** Generate summary stats

	foreach v in medhhinc medgrossrent {

		gen `v' = exp(l`v')
		summ `v'
		local `v' = r(mean)
		insert_into_file using table1.csv, key(`v'_421a) value(``v'') format(%12.0fc)
		drop `v'
		
	}
		
	foreach v of varlist 	sh_poor sh_lths sh_hsdeg sh_somecoll sh_coll sh_postgrad ///
							sh_white sh_black sh_hispanic sh_asian sh_renter {
		
		replace `v' = 100*`v'
		summ `v'
		local `v' = r(mean)
		insert_into_file using table1.csv, key(`v'_421a) value(``v'') format(%12.1f)
		
	}
	
	summ medage
	local medage = r(mean)
	insert_into_file using table1.csv, key(medage_421a) value(`medage') format(%12.1fc)
	drop medage
	
	summ yearbuilt if yearbuilt >= 2003
	local yearbuilt = r(mean)
	insert_into_file using table1.csv, key(yearbuilt_421a) value(`yearbuilt') format(%12.0f)
	
	gen area_per_unit = resarea / unitsres
	summ area_per_unit
	local area_per_unit = r(mean)
	insert_into_file using table1.csv, key(area_per_unit) value(`area_per_unit') format(%12.0f)
	
	tabstat borough, by(borough) stats(n) save
	
	mat stats1 = r(Stat1)
	mat stats2 = r(Stat2)
	mat stats3 = r(Stat3)
	mat stats4 = r(Stat4)
	mat stats5 = r(Stat5)
	
	local v1 = 0.2*stats1[1,1]
	local v2 = 0.2*stats2[1,1]
	local v3 = 0.2*stats3[1,1]
	local v4 = 0.2*stats4[1,1]
	local v5 = 0.2*stats5[1,1]
	
	insert_into_file using table1.csv, key(n1_421a) value(`v1') format(%12.0fc)
	insert_into_file using table1.csv, key(n2_421a) value(`v2') format(%12.0fc)
	insert_into_file using table1.csv, key(n3_421a) value(`v3') format(%12.0fc)
	insert_into_file using table1.csv, key(n4_421a) value(`v4') format(%12.0fc)
	insert_into_file using table1.csv, key(n5_421a) value(`v5') format(%12.0fc)
	
********************************************************************************
* LIHTC
********************************************************************************
	
*** Load data

	use "$data/clean/non_421a_units.dta", clear

	* Expand to match characteristics of LIHTC units
	keep if bldg_type == 2
	expand assisted_unit_count	
	
*** Generate summary stats

	foreach v in medhhinc medgrossrent {

		gen `v' = exp(l`v')
		summ `v'
		local `v' = r(mean)
		insert_into_file using table1.csv, key(`v'_lihtc) value(``v'') format(%12.0fc)
		drop `v'
		
	}
	
	gen sh_social = assisted_unit_count / total_unit_count
	
	foreach v of varlist 	sh_poor sh_lths sh_hsdeg sh_somecoll sh_coll sh_postgrad ///
							sh_white sh_black sh_hispanic sh_asian sh_renter sh_social {
		
		replace `v' = 100*`v'
		summ `v'
		local `v' = r(mean)
		insert_into_file using table1.csv, key(`v'_lihtc) value(``v'') format(%12.1f)
		
	}
	
	summ medage
	local medage = r(mean)
	insert_into_file using table1.csv, key(medage_lihtc) value(`medage') format(%12.1fc)
	drop medage
	
	tabstat borough, by(borough) stats(n) save
	
	mat stats1 = r(Stat1)
	mat stats2 = r(Stat2)
	mat stats3 = r(Stat3)
	mat stats4 = r(Stat4)
	mat stats5 = r(Stat5)
	
	local v1 = stats1[1,1]
	local v2 = stats2[1,1]
	local v3 = stats3[1,1]
	local v4 = stats4[1,1]
	local v5 = stats5[1,1]
	
	insert_into_file using table1.csv, key(n1_lihtc) value(`v1') format(%12.0fc)
	insert_into_file using table1.csv, key(n2_lihtc) value(`v2') format(%12.0fc)
	insert_into_file using table1.csv, key(n3_lihtc) value(`v3') format(%12.0fc)
	insert_into_file using table1.csv, key(n4_lihtc) value(`v4') format(%12.0fc)
	insert_into_file using table1.csv, key(n5_lihtc) value(`v5') format(%12.0fc)
	
********************************************************************************
* Project-Based Section 8
********************************************************************************
	
*** Load data

	use "$data/clean/non_421a_units.dta", clear

	* Expand to match characteristics of LIHTC units
	keep if bldg_type == 1
	expand assisted_unit_count	
	
*** Generate summary stats

	foreach v in medhhinc medgrossrent {

		gen `v' = exp(l`v')
		summ `v'
		local `v' = r(mean)
		insert_into_file using table1.csv, key(`v'_pbsection8) value(``v'') format(%12.0fc)
		drop `v'
		
	}
	
	gen sh_social = assisted_unit_count / total_unit_count
	
	foreach v of varlist 	sh_poor sh_lths sh_hsdeg sh_somecoll sh_coll sh_postgrad ///
							sh_white sh_black sh_hispanic sh_asian sh_renter sh_social {
		
		replace `v' = 100*`v'
		summ `v'
		local `v' = r(mean)
		insert_into_file using table1.csv, key(`v'_pbsection8) value(``v'') format(%12.1f)
		
	}
	
	summ medage
	local medage = r(mean)
	insert_into_file using table1.csv, key(medage_pbsection8) value(`medage') format(%12.1fc)
	drop medage
	
	tabstat borough, by(borough) stats(n) save
	
	mat stats1 = r(Stat1)
	mat stats2 = r(Stat2)
	mat stats3 = r(Stat3)
	mat stats4 = r(Stat4)
	mat stats5 = r(Stat5)
	
	local v1 = stats1[1,1]
	local v2 = stats2[1,1]
	local v3 = stats3[1,1]
	local v4 = stats4[1,1]
	local v5 = stats5[1,1]
	
	insert_into_file using table1.csv, key(n1_pbsection8) value(`v1') format(%12.0fc)
	insert_into_file using table1.csv, key(n2_pbsection8) value(`v2') format(%12.0fc)
	insert_into_file using table1.csv, key(n3_pbsection8) value(`v3') format(%12.0fc)
	insert_into_file using table1.csv, key(n4_pbsection8) value(`v4') format(%12.0fc)
	insert_into_file using table1.csv, key(n5_pbsection8) value(`v5') format(%12.0fc)
	
********************************************************************************
* Tenant-Based Section 8
********************************************************************************

*** Load data

	import delimited "$data/raw/OtherHousingAssistance/Section8/hudPicture2015_360148.csv", encoding(ISO-8859-1) clear
	
	keep code reported averagefamilyexpenditurepermonth averagehudexpenditurepermonth
	
*** Clean data

	rename code geoid
	rename reported occ_units
	
	gen borough = substr(geoid,1,5)
	destring borough, replace force
	
	replace borough = 1 if borough == 36061
	replace borough = 2 if borough == 36005
	replace borough = 3 if borough == 36047
	replace borough = 4 if borough == 36081 
	replace borough = 5 if borough == 36085
	
	drop if borough > 5
	
	gen ct2010 = substr(geoid,6,6)
	destring ct2010, replace force
	
	drop if missing(borough) | missing(ct2010)
	drop geoid

	rename averagefamilyexpenditurepermonth private_rent_monthly
	rename averagehudexpenditurepermonth public_rent_monthly
	
	replace private_rent_monthly = . if private_rent_monthly == -4
	replace public_rent_monthly = . if public_rent_monthly == -4
	
	order borough ct2010 occ_units private_rent_monthly public_rent_monthly
	
*** Merge with characteristics data

	preserve
	
	import delimited "$data/raw/CensusAreaCharacteristics/final/tract_characteristics.csv", encoding(ISO-8859-1) clear
	
	destring *, force replace
	
	gen borough = .
	replace borough = 1 if county == 61
	replace borough = 2 if county == 5
	replace borough = 3 if county == 47
	replace borough = 4 if county == 81 
	replace borough = 5 if county == 85
	drop county
	
	tempfile tract_characteristics
	save `tract_characteristics', replace
	
	restore
	
	merge m:1 borough ct2010 using `tract_characteristics', nogen keep(3)
	
*** Generate summary stats
	
	foreach v in medhhinc medgrossrent {

		summ `v' [aw=occ_units]
		local `v' = r(mean)
		insert_into_file using table1.csv, key(`v'_tbsection8) value(``v'') format(%12.0fc)
		
	}
	
	foreach v of varlist 	sh_poor sh_lths sh_hsdeg sh_somecoll sh_coll sh_postgrad ///
							sh_white sh_black sh_hispanic sh_asian sh_renter {
		
		replace `v' = 100*`v'
		summ `v' [aw=occ_units]
		local `v' = r(mean)
		insert_into_file using table1.csv, key(`v'_tbsection8) value(``v'') format(%12.1f)
		
	}
	
	summ medage [aw=occ_units]
	local medage = r(mean)
	insert_into_file using table1.csv, key(medage_tbsection8) value(`medage') format(%12.1fc)
	drop medage
	
	expand occ_units
	
	tabstat borough, by(borough) stats(n) save
	
	mat stats1 = r(Stat1)
	mat stats2 = r(Stat2)
	mat stats3 = r(Stat3)
	mat stats4 = r(Stat4)
	mat stats5 = r(Stat5)
	
	local v1 = stats1[1,1]
	local v2 = stats2[1,1]
	local v3 = stats3[1,1]
	local v4 = stats4[1,1]
	local v5 = stats5[1,1]
	
	insert_into_file using table1.csv, key(n1_tbsection8) value(`v1') format(%12.0fc)
	insert_into_file using table1.csv, key(n2_tbsection8) value(`v2') format(%12.0fc)
	insert_into_file using table1.csv, key(n3_tbsection8) value(`v3') format(%12.0fc)
	insert_into_file using table1.csv, key(n4_tbsection8) value(`v4') format(%12.0fc)
	insert_into_file using table1.csv, key(n5_tbsection8) value(`v5') format(%12.0fc)
	
********************************************************************************
* Public Housing
********************************************************************************
	
*** Load data

	use "$data/clean/non_421a_units.dta", clear

	* Expand to match characteristics of NYCHA units
	keep if bldg_type == 3
	expand assisted_unit_count	
	
*** Generate summary stats

	foreach v in medhhinc medgrossrent {

		gen `v' = exp(l`v')
		summ `v'
		local `v' = r(mean)
		insert_into_file using table1.csv, key(`v'_nycha) value(``v'') format(%12.0fc)
		drop `v'
		
	}
	
	gen sh_social = assisted_unit_count / total_unit_count
	
	foreach v of varlist 	sh_poor sh_lths sh_hsdeg sh_somecoll sh_coll sh_postgrad ///
							sh_white sh_black sh_hispanic sh_asian sh_renter sh_social {
		
		replace `v' = 100*`v'
		summ `v'
		local `v' = r(mean)
		insert_into_file using table1.csv, key(`v'_nycha) value(``v'') format(%12.1f)
		
	}
	
	summ medage
	local medage = r(mean)
	insert_into_file using table1.csv, key(medage_nycha) value(`medage') format(%12.1fc)
	drop medage
	
	tabstat borough, by(borough) stats(n) save
	
	mat stats1 = r(Stat1)
	mat stats2 = r(Stat2)
	mat stats3 = r(Stat3)
	mat stats4 = r(Stat4)
	mat stats5 = r(Stat5)
	
	local v1 = stats1[1,1]
	local v2 = stats2[1,1]
	local v3 = stats3[1,1]
	local v4 = stats4[1,1]
	local v5 = stats5[1,1]
	
	insert_into_file using table1.csv, key(n1_nycha) value(`v1') format(%12.0fc)
	insert_into_file using table1.csv, key(n2_nycha) value(`v2') format(%12.0fc)
	insert_into_file using table1.csv, key(n3_nycha) value(`v3') format(%12.0fc)
	insert_into_file using table1.csv, key(n4_nycha) value(`v4') format(%12.0fc)
	insert_into_file using table1.csv, key(n5_nycha) value(`v5') format(%12.0fc)
	
********************************************************************************
* All Rental Units
********************************************************************************
	
*** Load data

	use "$data/clean/census_area_characteristics.dta", clear

	expand ct_occ_renter_units	
	
*** Generate summary stats

	foreach v in medhhinc medgrossrent {

		gen `v' = exp(l`v')
		summ `v'
		local `v' = r(mean)
		insert_into_file using table1.csv, key(`v'_renter) value(``v'') format(%12.0fc)
		drop `v'
		
	}
		
	foreach v of varlist 	sh_poor sh_lths sh_hsdeg sh_somecoll sh_coll sh_postgrad ///
							sh_white sh_black sh_hispanic sh_asian sh_renter {
		
		replace `v' = 100*`v'
		summ `v'
		local `v' = r(mean)
		insert_into_file using table1.csv, key(`v'_renter) value(``v'') format(%12.1f)
		
	}
	
	summ medage
	local medage = r(mean)
	insert_into_file using table1.csv, key(medage_renter) value(`medage') format(%12.1fc)
	drop medage
	
	tabstat borough, by(borough) stats(n) save
	
	mat stats1 = r(Stat1)
	mat stats2 = r(Stat2)
	mat stats3 = r(Stat3)
	mat stats4 = r(Stat4)
	mat stats5 = r(Stat5)
	
	local v1 = stats1[1,1]
	local v2 = stats2[1,1]
	local v3 = stats3[1,1]
	local v4 = stats4[1,1]
	local v5 = stats5[1,1]
	
	insert_into_file using table1.csv, key(n1_renter) value(`v1') format(%12.0fc)
	insert_into_file using table1.csv, key(n2_renter) value(`v2') format(%12.0fc)
	insert_into_file using table1.csv, key(n3_renter) value(`v3') format(%12.0fc)
	insert_into_file using table1.csv, key(n4_renter) value(`v4') format(%12.0fc)
	insert_into_file using table1.csv, key(n5_renter) value(`v5') format(%12.0fc)
	
********************************************************************************
* Create table
********************************************************************************
	
	cat table1.csv

	cap erase "$tables/output/table1.tex"

	cd "$code/modules"
	capture table_from_tpl, t("$tables/templates/table1.tex") ///
							r("$tables/estimates/table1.csv") ///
							o("$tables/output/table1.tex") 
				
	capture table_from_tpl, t("$tables/templates/table1.tex") ///
							r("$tables/estimates/table1.csv") ///
							o("$tables_overleaf/table1.tex") 
	
	exit
